<?php
/**
 * Created by a front-end programmer.
 * User: Xwen
 * Date: 2018/2/8
 * Time: 11:58
 * Mail:lauwencn@gmail.com
 */
class Model{
    // 保存连接信息
    public static $link = NULL;
    // 保存表信息
    protected $table = NULL;
    // 初始化sql信息
    private $opt;
    // 记录发送过sql
    public static $sqls = array();
    //记录用户条件，分页需要使用
    private $_field = NULL;
    private $_where = NULL;
    private $_having = NULL;
    private $_group = NULL;
    private $_order = NULL;
    private $_limit = NULL;


    public function __construct($table=NULL){
        // 根据前缀拼接表名
        $this->table = is_null($table) ? C('DB_PREFIX').$table : C('DB_PREFIX').$table;
        // 连接数据库
        $this->__connect();
        // 初始化sql信息
        $this->_opt();
    }

    /**
     * 更新数据库
     * @param null $data
     * @return mixed
     */
    public function update($data=NULL){
        if(empty($this->opt['where'])) half('修改操作必须有where条件');
        if(is_null($data)) $data = $_POST;
        $values = '';
        foreach ($data as $k => $v){
            $values .= "`".$this->__safe_str($k)."`='".$this->__safe_str($v)."',";
        }
        $values = trim($values,',');
        $sql = "UPDATE ".$this->table." SET ".$values.$this->opt['where'];
        return $this->exe($sql);
    }
    /**
     * 添加数据方法
     * @param null $data  不传时直接取post值
     * @return mixed
     */
    public function add($data=NULL){
        if(is_null($data)) $data = $_POST;
        $fields = '';
        $values = '';
        foreach ($data as $f => $v){
            $fields .= "`".$this->__safe_str($f)."`,";
            $values .= "'".$this->__safe_str($v)."',";
        }
        $fields = trim($fields,',');
        $values = trim($values,',');
        $sql = "INSERT INTO ".$this->table."(".$fields.") VALUES(".$values.")";
        return $this->exe($sql);
    }
    /**
     * 删除操作
     * @return mixed
     */
    public function delete(){
        if(empty($this->opt['where'])) half('删除操作必须有where条件');
        $sql = "DELETE FROM ".$this->table.$this->opt['where'];
        return $this->exe($sql);
    }
    /**
     * find()方法别名
     * @return array|mixed
     */
    public function search(){
        return  $this->find();
    }
    /**
     * 查询第一条数据
     * @return array|mixed
     */
    public function find(){
        $data = $this->limit(1)->all();
        $data = current($data);
        return $data;
    }
    /**
     * 查询所有信息
     * @return array
     */
    public function all(){
        $sql = "SELECT ".$this->opt['field']." FROM ".$this->table.$this->opt['where'].$this->opt['group'].$this->opt['having'].$this->opt['order'].$this->opt['limit'];
        return $this->query($sql);
    }
    /**
     * @param $where 查询条件
     * @return $this
     */
    public function where($where){
        $this->opt['where'] = " WHERE ".$where;
        $this->_where = $where;
        return $this;
    }
    /**
     * @param $order 排序条件
     * @return $this
     */
    public function order($order){
        $this->opt['order'] = " ORDER BY ".$order;
        $this->_order = $order;
        return $this;
    }
    /**
     * @param $limit 限制条件
     * @return $this
     */
    public function limit($limit){
        $this->opt['limit'] = " LIMIT ".$limit;
        $this->_limit = $limit;
        return $this;
    }
    /**
     * @param $having 筛选分组条件
     * @return $this
     */
    public function having($having){
        $this->opt['having'] = " HAVING ".$having;
        $this->_having = $having;
        return $this;
    }
    /**
     * @param $group 分组条件
     * @return $this
     */
    public function group($group){
        $this->opt['group'] = " GROUP BY ".$group;
        $this->_group = $group;
        return $this;
    }
    /**
     * @param $field 需要查询的字段名
     * @return $this 当前对象
     */
    public function field($field){
        $this->opt['field'] = $field;
        $this->_field = $field;
        return $this;
    }

    /**
     * 数据库操作执行方法
     * @param $sql
     * @return mixed
     */
    public function exe($sql){
        self::$sqls[] = $sql;
        $link = self::$link;
        $bool = $link->query($sql);
        $this->_opt();
        if(is_object($bool)){
            half("请用query()方法发送查询语句！");
        }
        if($bool){
            return $link->insert_id ? $link->insert_id : $link->affected_rows;
        }else{
            half('<br/>mysql错误：'.$link->error.'<br/>SQL:'.$sql);
        }
    }
    /**
     * 数据库查询执行方法
     * @param $sql
     * @return array
     */
    public function query($sql){
        self::$sqls[] = $sql;
        $link = self::$link;
        $result = $link->query($sql);
        if($link->errno) half('<br/>mysql错误：'.$link->error.'<br/>SQL:'.$sql);
        $rows = array();
        while($row = $result->fetch_assoc()){
            $rows[] = $row;
        }
        $result->free();
        $this->_opt();
        return $rows;
    }
    /**
     * 初始化sql信息
     */
    private function _opt(){
        $this->opt = array(
            'field'     =>      '*',
            'where'     =>      '',
            'having'    =>      '',
            'group'     =>      '',
            'order'     =>      '',
            'limit'     =>      ''
        );
    }
    /**
     * 连接配置的数据库
     */
    private function __connect(){
        if(is_null(self::$link)){
            $db = C('DB_DATABASE');
            if(empty($db)) half('请先配置数据库信息！');
            $link = new Mysqli(C('DB_HOST'), C('DB_USER'), C('DB_PASSWORD'),$db, C('DB_PORT'));
            if($link->connect_error) half('数据库连接失败，请检查配置项！');
            $link->set_charset(C('DB_CHARSET'));
            self::$link = $link;
        }
    }
    /**
     * 安全处理函数
     * @param $str
     * @return mixed
     */
    private function __safe_str($str){
        if(get_magic_quotes_gpc()){
            $str = stripslashes($str);
        }
        return self::$link->real_escape_string($str);
    }

    /**
     * @param int $page 当前页
     * @param int $rows 每页数据数
     * @return array
     */
    public function pagination($page=1, $rows = 5){
        $page = $this->__safe_str($page);
        $rows = $this->__safe_str($rows);
        $sql = 'SELECT COUNT(*) FROM '.$this->table;
        if($this->_where){
            $sql .= " WHERE ".$this->_where;
        }
        if($this->_group){
            $sql .= " GROUP BY ".$this->_group;
        }
        if($this->_having){
            $sql .= " HAVING ".$this->_having;
        }
        if($this->_order){
            $sql .= " ORDER BY ".$this->_order;
        }

//        $sumRows = current($this->query('SELECT COUNT(*) FROM '.$this->table));
        $sumRows = current($this->query($sql));
        $sumRows = current($sumRows);
        $sumPage = ceil($sumRows/$rows);
        if($page > $sumPage) $page = $sumPage;
        if ($page <= 0) $page = 1;

        $offset = ($page-1) * $rows;
        $limit = $offset.",".$rows;

        $db = $this;
        if($this->_field){
            $db = $this->field($this->_field);
        }
        if($this->_where){
            $db = $this->where($this->_where);
        }
        if($this->_group){
            $db = $this->group($this->_group);
        }
        if($this->_having){
            $db = $this->having($this->_having);
        }
        if($this->_order){
            $db = $this->order($this->_order);
        }
        if($this->_limit){
            half('pagination()分页方法不支持设置limit限制');
        }

        $data = $db->limit($limit)->all();

        $startRows = $offset + 1;
        $endRows = $offset + $rows;
        if($page == $sumPage) $endRows = $sumRows;
        $prePage = $page - 1;
        $nextPage = $page + 1;

        return [
            'sumRows'           =>      $sumRows,           // 返回数据总行数
            'startRows'         =>      $startRows,         // 开始行号
            'endRows'           =>      $endRows,           // 结束行号
            'currentPage'       =>      $page,              // 当前页码
            'sumPage'           =>      $sumPage,           // 总页数
            'prePage'           =>      $prePage,           // 上页页码
            'nextPage'          =>      $nextPage,          // 下页页码
            'returnData'        =>      $data,              // 查询到的当前页数据
        ];
    }
}


?>